package educrypt.database;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Observable;

import educrypt.exceptions.EduCryptException;
import educrypt.i18n.Messages;
import educrypt.io.Input;
import educrypt.resources.EduCryptResources;

public class EduCryptDatabase extends Observable {

	public static String DATABASE_PATH = System.getProperty("user.home")
			+ "/.educrypt/db/educryptdb.h2.db";

	/* Singleton */
	private EduCryptDatabase() {
	}

	public static EduCryptDatabase getInstance() {
		return SingletonHolder.instance;
	}

	private static class SingletonHolder {
		public static final EduCryptDatabase instance = new EduCryptDatabase();
	}

	/* END Singleton */

	public boolean existsDatabase() {
		boolean databaseExists = false;
		File databaseFile = new File(DATABASE_PATH);
		databaseExists = databaseFile.exists() && existTables();
		return databaseExists;
	}

	private boolean existTables() {
		String sqlExistsTableCiphers = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='PUBLIC' AND TABLE_NAME ='CIPHER'";
		int numberOfTables = -1;
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlExistsTableCiphers);
			if (rs.next()) {
				numberOfTables = rs.getInt(1);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (numberOfTables == 1);
	}

	public void createDatabase() throws EduCryptException {
		String sqlCreationScript = Input.readTextFile(EduCryptResources
				.getDbScriptPath());
		createDatabase(sqlCreationScript);
	}

	private void createDatabase(String sqlCreationScript)
			throws EduCryptException {
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			st.execute(sqlCreationScript);
			st.close();
		} catch (SQLException e) {
			throw new EduCryptException(Messages.getString("errDBCreation"));
		}
	}

	public void importDatabase(String scriptFilePath) throws EduCryptException {
		final char VALIDATION_CHAR = ';'; // h2 insert this char at the
											// beginning of the script, is going
											// to be used as a validation
											// character to detect invalid files

		// Reading script file
		String sqlCreationScript = Input.readTextFile(scriptFilePath);

		// Checking if the file is a valid script generated by eduCrypt
		if (sqlCreationScript.charAt(0) != VALIDATION_CHAR) {
			throw new EduCryptException(
					Messages.getString("errDBInvalidScript"));
		}

		// Deleting actual database
		boolean deleted = this.deleteDatabase();
		if (!deleted) {
			throw new EduCryptException(Messages.getString("errDBImport"));
		}

		// Removing validation character from the script
		sqlCreationScript = sqlCreationScript.replaceFirst("^;", "");

		// Creating database
		createDatabase(sqlCreationScript);

		// Notifying changes
		this.setChanged();
		this.notifyObservers();
	}

	private boolean deleteDatabase() {
		Connector.releaseConnection();
		File dataBaseFile = new File(DATABASE_PATH);
		return dataBaseFile.delete();
	}

	public void exportDatabase(String scriptFile) throws EduCryptException {
		String sqlExportDatabase = "SCRIPT TO ?";
		try {
			Connection conn = Connector.getConnection();
			PreparedStatement ps = conn.prepareStatement(sqlExportDatabase);
			ps.setString(1, scriptFile);
			ps.executeQuery();
			ps.close();
		} catch (SQLException e) {
			throw new EduCryptException(Messages.getString("errDBExport"));
		}
	}
}
